﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DuLieu;
using System.Data.OleDb;

namespace DAO
{
    public static class QuanDAO
    {
        // Insert Quan to Database.
        public static Boolean InsertDuong(QuanDTO quan)
        {
            QuanDTO newQuan = GetQuanByID(quan.MaQuan);
            
            if (newQuan != null)
                return false;

            try
            {
                DataProvider.OpenConnection();

                string insertCmd = "INSERT INTO QuanHuyen(MaQuanHuyen, TenQuanHuyen) values(" + quan.MaQuan + "," + "'" + quan.TenQuan + "'" + ")";
                DataProvider.ExecuteNonQuery(insertCmd);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                DataProvider.CloseConnection();
            }

            return true;
        }

        // Delete Quan into Database.
        public static Boolean DeleteQuan(int id)
        {
            QuanDTO delQuan = GetQuanByID(id);

            if (delQuan == null)
                return false;

            try
            {
                DataProvider.OpenConnection();
                
                string delCmd = "DELETE FROM QuanHuyen WHERE MaQuanHuyen = " + id;
                DataProvider.ExecuteNonQuery(delCmd);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                DataProvider.CloseConnection();
            }

            return true;
        }

        // Update Quan into Database.
        public static Boolean UpdateQuan(QuanDTO quan)
        {
            QuanDTO tempQuan = GetQuanByID(quan.MaQuan);

            if (tempQuan == null)
                return false;

            try
            {
                DataProvider.OpenConnection();

                string updateCmd = "UPDATE QuanHuyen SET TenQuanHuyen = " + quan.TenQuan + "WHERE MaQuanHuyen = " + quan.MaQuan;
                DataProvider.ExecuteNonQuery(updateCmd);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                DataProvider.CloseConnection();
            }

            return true;
        }

        // Get all Quan from Database.
        public static List<QuanDTO> GetDanhSachQuan()
        {
            List<QuanDTO> resultList = null;

            try
            {
                DataProvider.OpenConnection();
                
                resultList = new List<QuanDTO>();

                string selCmd = "SELECT * FROM QuanHuyen";
                OleDbDataReader reader = DataProvider.ExecuteReader(selCmd);

                while (reader.Read())
                { 
                    QuanDTO quan = new QuanDTO();
                    if (!reader.IsDBNull(0))
                        quan.MaQuan = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        quan.TenQuan = reader.GetString(1);

                    resultList.Add(quan);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                DataProvider.CloseConnection();
            }

            return resultList;
        }

        // Get Quan by ID from Database
        public static QuanDTO GetQuanByID(int id)
        {
            QuanDTO result = null;

            try
            {
                DataProvider.OpenConnection();
                String selCmd = "SELECT * FROM QuanHuyen WHERE MaQuanHuyen = " + id;
                OleDbDataReader reader = DataProvider.ExecuteReader(selCmd);

                if (reader.Read())
                {
                    result = new QuanDTO();
                    if (!reader.IsDBNull(0))
                        result.MaQuan = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        result.TenQuan = reader.GetString(1);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                DataProvider.CloseConnection();
            }

            return result;
        }

        // Get Name by ID from Database
        public static String GetNameByID(int id)
        {
            String result = null;

            try
            {
                DataProvider.OpenConnection();
                String selCmd = "SELECT TenQuanHuyen FROM QuanHuyen WHERE MaQuanHuyen = " + id;
                OleDbDataReader reader = DataProvider.ExecuteReader(selCmd);

                if (reader.Read())
                {
                    if (!reader.IsDBNull(0))
                        result = reader.GetString(0);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                DataProvider.CloseConnection();
            }

            return result;
        }

        // Get Quan by Name from Database
        public static QuanDTO GetQuanByName(string name)
        {
            QuanDTO result = null;

            try
            {
                DataProvider.OpenConnection();
                String selCmd = "SELECT * FROM QuanHuyen WHERE TenQuanHuyen = " + name;
                OleDbDataReader reader = DataProvider.ExecuteReader(selCmd);

                if (reader.Read())
                {
                    result = new QuanDTO();
                    if (!reader.IsDBNull(0))
                        result.MaQuan = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        result.TenQuan = reader.GetString(1);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                DataProvider.CloseConnection();
            }

            return result;
        }

    }
}
